package banco;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import dominio.Cliente;
import dominio.Tipo_Planilha;
import fachada.FachadaCliente_Planilha;

public class Cliente_PlanilhaDAO implements FachadaCliente_Planilha{
		
		@SuppressWarnings("finally")
		public Tipo_Planilha selectCliente_Planilha(int id) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        Tipo_Planilha tp = new Tipo_Planilha();        
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("select * from cliente_planilha where id = ?");
	            stmt.setInt(1, id);
	            rs = stmt.executeQuery();
	            while (rs.next()){
	                tp.setId(rs.getInt("id"));
	                tp.setNome_panilha(rs.getString("nome_planilha"));
	            }
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
		        return tp;
	        }

}
		
		@SuppressWarnings("finally")
		public Collection selectCliente_Planilha() throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        Collection<Cliente> res = new ArrayList<Cliente>();
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("select  cliente.cnpj_cliente, cliente.nome_cliente, cliente_planilha.id_planilha " +
	            		"from cliente_planilha inner join cliente on (cliente_planilha.cnpj_cliente = cliente.cnpj_cliente)");
	            rs = stmt.executeQuery();
	            while (rs.next()){
	            	Cliente c = new Cliente();
	                c.setCnpj(rs.getString("cnpj_cliente"));
	                c.setNome(rs.getString("nome_cliente"));
	                c.setId_planilha(rs.getInt("id_planilha"));
	                res.add(c);
	            }
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
		        return res;
	        }

		}
		
		@SuppressWarnings("finally")
		public Collection selectClientes_Planilha(int id) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        Collection<Cliente> res = new ArrayList<Cliente>();
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("select cliente.cnpj_cliente, cliente.nome_cliente from cliente_planilha" +
	            		" inner join cliente on (cliente.cnpj_cliente = cliente_planilha.cnpj_cliente) where id_planilha = ?");
	            stmt.setInt(1, id);
	            rs = stmt.executeQuery();
	            while (rs.next()){
	            	Cliente c = new Cliente();
	                c.setCnpj(rs.getString("cnpj_cliente"));
	                c.setNome(rs.getString("nome_cliente"));
	                res.add(c);
	            }
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
		        return res;
	        }

}
		
		public void insertCliente_Planilha(Cliente c, Tipo_Planilha tp) throws ClassNotFoundException, IOException {
	        
	        Connection conn = null;
	        PreparedStatement stmt = null;
	        ResultSet rs = null;
	        
	        try {
	            conn = Banco.getConexaoEXTRATOPAES();
	            stmt = conn.prepareStatement("insert into cliente_planilha values (?, ?)");
	            stmt.setString(1, c.getCnpj().trim());
	            stmt.setInt(2, tp.getId());            
	            stmt.executeUpdate();
	        }
	        catch (SQLException ex) {
	            ex.printStackTrace();
	        }
	        
	        catch (Exception e) {
	        	e.printStackTrace();
	        }
	        finally {
		        Banco.closeConexao(conn, rs, stmt);
	        }
	    }
		
}
